# @Data  : 2019/12/25 10:33
# @Author: 
# @File  : ExcelOpts.py

import os
from ..const import prompt

try:
    import xlrd
except Exception as e:
    print(e)
    os.system("python -m pip install xlrd")
    import xlrd

try:
    from xlutils.copy import copy
except Exception as e:
    print(e)
    os.system("python -m pip install xlutils")
    from xlutils.copy import copy

BLANK_STR = ""


class ExcelOpts():

    def __init__(self, file_path, sheet_name="Sheet1"):
        # self.Row = xlwt.Row
        # self.Column = xlwt.Column
        self.sheet_name = sheet_name

        self.file_path = file_path
        self.book = self._read()
        self._first_sheet = self.book.sheets()[0]
        self._sheet_name = self.book.sheet_by_name(self.sheet_name)

        self.copy_book = copy(self.book)
        self._first_sheet_ = self.copy_book.get_sheet(0)
        self._sheet_name_ = self.copy_book.get_sheet(self.sheet_name)

    def _read(self):
        assert os.path.exists(self.file_path), prompt.file["fileLost"]
        book = xlrd.open_workbook(self.file_path)
        return book

    def write(self, row, col, info):
        sheet = self._sheet_name_ if self.sheet_name else self._first_sheet_
        sheet.write(row, col, info)

    def read(self, row, col_start, col_end):
        # Add by James at 20201123
        sheet = self._sheet_name if self.sheet_name else self._first_sheet
        return sheet.row_values(row, col_start, col_end)

    def clear(self, start_row: int = 0, end_row: int = None, start_column: int = 0,
              end_column: int = None):
        sheet = self._sheet_name_ if self.sheet_name else self._first_sheet_
        start_row = start_row
        end_row = end_row if end_row else self._calc_rows()
        start_column = start_column
        end_column = end_column if end_column else self._calc_cols()
        for row in range(start_row, end_row):
            for column in range(start_column, end_column):
                sheet.write(row, column, BLANK_STR)

    def save(self):
        self.copy_book.save(self.file_path)

    def _calc_cols(self):
        sheet = self._sheet_name if self.sheet_name else self._first_sheet
        return sheet.ncols

    def _calc_rows(self):
        sheet = self._sheet_name if self.sheet_name else self._first_sheet
        return sheet.nrows

    def read_config_rule(self):
        sheet = self._sheet_name if self.sheet_name else self._first_sheet
        all_rows = self._calc_rows()
        header = sheet.row_values(0)
        if header != ['配置组', '配置文件名', '配置项', '注释建议', '正确配置', '配置建议']:
            raise Exception("excel文件格式错误！请重新修改！")
        config_rule_info = dict()
        for i in range(1, all_rows):
            row_value = sheet.row_values(i)
            group_name = row_value[0]
            file_name = row_value[1]
            key = row_value[2]
            comment_suggestion = row_value[3]
            correct_value = row_value[4]
            config_suggestion = row_value[5]
            if not all([group_name, file_name, key]):
                continue
            if group_name not in config_rule_info:
                config_rule_info[group_name] = {
                    file_name: [[key, comment_suggestion, correct_value, config_suggestion]]}
            else:
                if file_name not in config_rule_info[group_name]:
                    config_rule_info[group_name][file_name] = [
                        [key, comment_suggestion, correct_value, config_suggestion]]
                else:
                    config_rule_info[group_name][file_name].append(
                        [key, comment_suggestion, correct_value, config_suggestion])

        return config_rule_info



if __name__ == '__main__':
    excel = ExcelOpts('D:\\workspace\\20201012_云测试介绍\\云测试推进工作.xlsx',"计划")
    result = excel.read(1000,1)
    print(result)